﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using EntityModel;



namespace DataAcessLayer
{
    public class DAL
    {

        private string connectionstring = "Data Source=ASPIRE5735Z\\SQLEXPRESS;Initial Catalog=CarDealer;Integrated Security=True";
       // private string connectionstring =@"F:\\CarDealer2.mdf";
      
        public bool logInCustomer(Customer customer)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            try
            {
                 ctx.Customer.Single(c => c.username == customer.username && c.password == customer.password);
                 return true;
            }
            catch
            {
                throw new Exception("Customer does not exist or wrong password");
            }

        }

        #region Employee
      

        public Employee LoginEmployee(Employee emp)
        {
            Employee aux;
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            try
            {
                aux = ctx.Employee.Single(e => e.username == emp.username);
                if (aux.password == emp.password)
                {
                    return aux;
                }
                else
                {
                    throw new Exception();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
        #endregion

        #region Private
        public Private LoginPrivateCustomer(Private customer)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Private aux = ctx.Private.Single(c => c.Customer.username == customer.Customer.username);
            if (aux.Customer.password == customer.Customer.password)
            {
                return aux;
            }
            else
            {
                return null;
            }

        }
        
        public Private getPrivateCustomer(string username) {

            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            try
            {
                return ctx.Private.Single(p => p.Customer.username == username);
            }
            catch {
                throw new Exception("Customer does not exist or wrong username");
            }
             
        }

        public List<Private> getPrivateCustomerList()
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            List<Private> res = new List<Private>();
            var list = from p in ctx.Private
                       select p;

            foreach (Private p in list)
            {
                res.Add(p);
            }
            return res;
        }

        public void updatePrivateCustomer(Private pc) {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Private aux = ctx.Private.Single(p =>p.id == pc.id);         
 
            aux.Customer.username = pc.Customer.username;
            aux.Customer.password = pc.Customer.password;
            aux.cpr = pc.cpr;
            aux.name = pc.name;
            aux.Customer.address_c = pc.Customer.address_c;
            aux.age = pc.age;
            aux.Customer.phone = pc.Customer.phone;
            aux.sex = pc.sex; 
            ctx.SubmitChanges();         
            
        }
       
        public void savePrivateCustomer(Private pc){
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            ctx.Private.InsertOnSubmit(pc);
            ctx.SubmitChanges();          
        }        

        public void deletePrivateCustomer(Private pc)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Private aux = ctx.Private.Single(p => p.id == pc.id);
            ctx.Private.DeleteOnSubmit(aux);
            ctx.Customer.DeleteOnSubmit(aux.Customer);
            ctx.SubmitChanges();            
        }
        #endregion

        #region Business

        //Recibes a business entity only with the username and password and check if the password if ok for that user, is is ok return true.
        

        public Business getBusinessCustomer(string username) {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            try
            {
                return ctx.Business.Single(b => b.Customer.username == username);
            }
            catch
            {
                throw new Exception("Customer does not exist or wrong username");
            }
        
        }

        public List<Business> getBusinessCustomerList() {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return ctx.Business.ToList();   
        
        }

        public void saveBusinessCustomer(Business bc)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            ctx.Business.InsertOnSubmit(bc);
            ctx.SubmitChanges();
        }

        public void updateBusinessCustomer(Business bc)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Business aux = ctx.Business.Single(b => b.id == bc.id);

            aux.Customer.username = bc.Customer.username;
            aux.Customer.password = bc.Customer.password;
            aux.Customer.address_c = bc.Customer.address_c;
            aux.Customer.phone = bc.Customer.phone;
            aux.b_identifier = bc.b_identifier;
            aux.company_name = bc.company_name;
            aux.contact_person = bc.contact_person;
            aux.fax = bc.fax;           
            ctx.SubmitChanges();   
        }

        public void deleteBusinessCustomer(Business bc)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Business aux = ctx.Business.Single(b => b.id == bc.id);
            ctx.Business.DeleteOnSubmit(aux);
            ctx.Customer.DeleteOnSubmit(aux.Customer);
            ctx.SubmitChanges();
        }      



        #endregion

        #region Vehicle
        public List<Fuel> getFuelTypes() {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return ctx.Fuel.ToList();
        }
        public List<States> getVehicleStates()
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return ctx.States.ToList();
        }
        public int getFuelId(string name) {
             CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return (ctx.Fuel.Single(f => f.fuel_name == name)).id;
            
        }
         public int getStateId(string name) {
             CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return (ctx.States.Single(s => s.state_name == name)).id;
            
        }

         public string getFuelName(int id)
         {
             CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
             return (ctx.Fuel.Single(f => f.id == id)).fuel_name;

         }

         public string getStateName(int id)
         {
             CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
             return (ctx.States.Single(s => s.id == id)).state_name;

         }

        #endregion

        #region Car

         public Car getCar(string regnumber)
         {
             CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
             return ctx.Car.Single(c => c.Vehicle.reg_number == regnumber);

         }

        public List<TypeCar> getCarTypes()
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return ctx.TypeCar.ToList();
        }
         
        public int getCarTypeId(string name) {
             CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return (ctx.TypeCar.Single(t => t.type_name== name)).id;
            
        }

        public string getCarTypeName(int id)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return (ctx.TypeCar.Single(t => t.id == id)).type_name;

        }
       
        public void saveCar(Car car) {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            ctx.Car.InsertOnSubmit(car);
            ctx.SubmitChanges();        
        }

        public void updateCar(Car car)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Car aux = ctx.Car.Single(c => c.id == car.id);

            aux.doors_number = car.doors_number;
            aux.type_car = car.type_car;
            aux.Vehicle.colour = car.Vehicle.colour;
            aux.Vehicle.fuel = car.Vehicle.fuel;
            aux.Vehicle.price = car.Vehicle.price;
            aux.Vehicle.reg_number = car.Vehicle.reg_number;
            aux.Vehicle.state = car.Vehicle.state;
            ctx.SubmitChanges();           
        }

        public void deleteCar(Car car)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Car aux = ctx.Car.Single(c => c.id == car.id);
            ctx.Car.DeleteOnSubmit(aux);
            ctx.Vehicle.DeleteOnSubmit(aux.Vehicle);
            ctx.SubmitChanges();           
        }

        public List<Car> getCarList()
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return ctx.Car.ToList();            
        }
       
        public List<Car> purchasedCarsList(Private p)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            var query = from Car c in ctx.Car
                        join CContract cc in ctx.CContract
                        on c.id equals cc.c_car
                        where cc.c_custumer == p.id
                        select c;
            List<Car> res = new List<Car>();
            
                foreach (Car car in query)
                {
                    res.Add(car);
                }
            
            return res;
        }

        public List<Car> availableCars()
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            var query = from Car c in ctx.Car                       
                        where c.Vehicle.state==1
                        select c;
            List<Car> res = new List<Car>();

            foreach (Car car in query)
            {
                res.Add(car);
            }

            return res;
        }


        #endregion

        #region Trucks
        public Truck getTruck(string regnumber)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return ctx.Truck.Single(t => t.Vehicle.reg_number == regnumber);

        }

        public void saveTruck(Truck truck)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            ctx.Truck.InsertOnSubmit(truck);
            ctx.SubmitChanges();
        }

        public void updateTruck(Truck truck)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Truck aux = ctx.Truck.Single(t => t.id == truck.id);
            aux.Vehicle.colour = truck.Vehicle.colour;
            aux.Vehicle.fuel = truck.Vehicle.fuel;
            aux.Vehicle.price = truck.Vehicle.price;
            aux.Vehicle.reg_number = truck.Vehicle.reg_number;
            aux.Vehicle.state = truck.Vehicle.state;
            aux.high = truck.high;
            aux.large = truck.large;
            aux.num_wheels = truck.num_wheels;
            aux.tonnage = truck.tonnage;
            aux.width = truck.width;
            ctx.SubmitChanges();
        }

        public void deleteTruck(Truck truck)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            Truck aux = ctx.Truck.Single(t => t.id == truck.id);
            ctx.Truck.DeleteOnSubmit(aux);
            ctx.Vehicle.DeleteOnSubmit(aux.Vehicle);
            ctx.SubmitChanges();
        }

        public List<Truck> getTruckList()
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            return ctx.Truck.ToList();
        }
        public List<Truck> leasedTruckList(Business b)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            var query = from Truck c in ctx.Truck
                        join CLeasing cc in ctx.CLeasing
                        on c.id equals cc.truck
                        where cc.c_customer == b.id
                        select c;
            List<Truck> res = new List<Truck>();

            foreach (Truck truck in query)
            {
                res.Add(truck);
            }

            return res;
        }
        public List<Truck> availableTrucks()
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            var query = from Truck c in ctx.Truck
                        where c.Vehicle.state == 1
                        select c;
            List<Truck> res = new List<Truck>();

            foreach (Truck t in query)
            {
                res.Add(t);
            }

            return res;
        }

       
        #endregion

        #region Contracts

        public void AddLeasingContract(CLeasing cl) { 
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            ctx.CLeasing.InsertOnSubmit(cl);
            ctx.SubmitChanges();          
        }

        public void AddCustomerContract(CContract cc)
        {
            CarDealerEntitiesDataContext ctx = new CarDealerEntitiesDataContext(connectionstring);
            ctx.CContract.InsertOnSubmit(cc);
            ctx.SubmitChanges();
        }

        #endregion
    }
}
